using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using Microsoft.ApplicationBlocks.Data;

namespace Customs.Components.GC
{
    public class DinhMuc
    {
        public static DataSet GetDanhSachDinhMucOfHopDong(string maHaiQuan, string maDoanhNghiep, string soHD, DateTime ngayKy)
        {
            string connectionString = ConnectionManager.GetConnectionString("SLXNK", maHaiQuan);
            StringBuilder query = new StringBuilder();
            query.Append("SELECT ");
            query.Append("LSP.So_HD			AS SoHopDong, ");
            query.Append("LSP.Ma_HQHD		AS MaHaiQuan, ");
            query.Append("LSP.DVGC			AS MaDoanhNghiep, ");
            query.Append("LSP.Ngay_Ky		AS NgayKy, ");
            query.Append("LSP.SPP_Code		AS MaSP, ");
            query.Append("LSP.NPLP_Code		AS MaNPL, ");
            query.Append("LSP.DMGC		AS DinhMuc, ");
            query.Append("LSP.TLHH		AS TyLeHaoHut ,");
            query.Append("LSP.Ghi_Chu		AS Ghi_Chu, ");
            query.Append("LSP.Mua_VN		AS NPL_TuCungUng ");
            query.Append("FROM DDMuc LSP ");
            query.Append("WHERE ");
            query.Append("LSP.Ma_HQHD = @MaHaiQuan AND ");
            query.Append("LSP.DVGC = @MaDoanhNghiep AND ");
            query.Append("LSP.So_HD = @SoHopDong AND ");
            query.Append("LSP.Ngay_Ky = @NgayKy ");

            SqlParameter[] parameters = new SqlParameter[4];

            parameters[0] = new SqlParameter("@MaHaiQuan", SqlDbType.Char, 6);
            parameters[0].Value = maHaiQuan;

            parameters[1] = new SqlParameter("@MaDoanhNghiep", SqlDbType.VarChar, 14);
            parameters[1].Value = maDoanhNghiep;

            parameters[2] = new SqlParameter("@SoHopDong", SqlDbType.VarChar, 50);
            parameters[2].Value = soHD;

            parameters[3] = new SqlParameter("@NgayKy", SqlDbType.DateTime);
            parameters[3].Value = ngayKy;

            return SqlHelper.ExecuteDataset(connectionString, CommandType.Text, query.ToString(), parameters);
        }

        public static DataSet GetDanhSach(string maHaiQuan, string maDoanhNghiep)
        {
            string connectionString = ConfigurationManager.ConnectionStrings["ConnectionString_SLXNK"].ConnectionString;
            StringBuilder query = new StringBuilder();
            query.Append("SELECT ");
            query.Append("LSP.So_HD			AS SoHopDong, ");
            query.Append("LSP.Ma_HQHD		AS MaHaiQuan, ");
            query.Append("LSP.DVGC			AS MaDoanhNghiep, ");
            query.Append("LSP.Ngay_Ky		AS NgayKy, ");
            query.Append("LSP.SPP_Code		AS MaSP, ");
            query.Append("LSP.NPLP_Code		AS MaNPL, ");
            query.Append("LSP.DMGC		AS DinhMuc, ");
            query.Append("LSP.TLHH		AS TyLeHaoHut ,");
            query.Append("LSP.Ghi_Chu		AS Ghi_Chu, ");
            query.Append("LSP.Mua_VN		AS NPL_TuCungUng ");
            query.Append("FROM DDMuc LSP ");
            query.Append("WHERE ");
            query.Append("LSP.Ma_HQHD = @MaHaiQuan AND ");
            query.Append("LSP.DVGC = @MaDoanhNghiep  ");

            SqlParameter[] parameters = new SqlParameter[2];

            parameters[0] = new SqlParameter("@MaHaiQuan", SqlDbType.Char, 6);
            parameters[0].Value = maHaiQuan;

            parameters[1] = new SqlParameter("@MaDoanhNghiep", SqlDbType.VarChar, 14);
            parameters[1].Value = maDoanhNghiep;

            return SqlHelper.ExecuteDataset(connectionString, CommandType.Text, query.ToString(), parameters);
        }
        public static DataSet GetDanhSachDinhMuc(long index, string maHaiQuan, string maDoanhNghiep)
        {
            string connectionString = ConfigurationManager.ConnectionStrings["ConnectionString_SLXNK"].ConnectionString;
            StringBuilder query = new StringBuilder();
            query.Append("SELECT * from ( ");
            query.Append("LSP.So_HD			AS SoHopDong, ");
            query.Append("LSP.Ma_HQHD		AS MaHaiQuan, ");
            query.Append("LSP.DVGC			AS MaDoanhNghiep, ");
            query.Append("LSP.Ngay_Ky		AS NgayKy, ");
            query.Append("LSP.SPP_Code		AS MaSP, ");
            query.Append("LSP.NPLP_Code		AS MaNPL, ");
            query.Append("LSP.DMGC		AS DinhMuc, ");
            query.Append("LSP.TLHH		AS TyLeHaoHut ,");
            query.Append("LSP.Ghi_Chu		AS Ghi_Chu, ");
            query.Append("LSP.Mua_VN		AS NPL_TuCungUng ");
            query.Append("row_number() OVER (ORDER BY So_HD) AS rowid ");
            query.Append("FROM DDMuc LSP ");
            query.Append("WHERE ");
            query.Append("LSP.Ma_HQHD = @MaHaiQuan AND ");
            query.Append("LSP.DVGC = @MaDoanhNghiep  "); 
            query.Append(" )a where rowid=@rowid ORDER BY SoHopDong");

            SqlParameter[] parameters = new SqlParameter[3];

            parameters[0] = new SqlParameter("@rowid", SqlDbType.BigInt);
            parameters[0].Direction = ParameterDirection.Input;
            parameters[0].Value = index;
            parameters[1] = new SqlParameter("@MaHaiQuan", SqlDbType.Char);
            parameters[1].Size = 6;
            parameters[1].Direction = ParameterDirection.Input;
            parameters[1].Value = maHaiQuan;

            parameters[2] = new SqlParameter("@MaDoanhNghiep", SqlDbType.VarChar);
            parameters[2].Size = 14;
            parameters[2].Direction = ParameterDirection.Input;
            parameters[2].Value = maDoanhNghiep;

            DataSet ds = SqlHelper.ExecuteDataset(connectionString, CommandType.Text, query.ToString(), parameters);
            if (ds.Tables[0].Rows.Count == 0)
                return null;
            return ds;

        }
    }
}
